import traceback
import json
import re
import pandas as pd
import datetime

import time
from flask import Flask, request, jsonify
from pymongo import MongoClient

import os
import uuid
import MySQLdb.cursors

app = Flask(__name__)

client = MongoClient('47.100.19.231', 27018)
db = client.admin
db.authenticate("quant", "Qweasd123")
db = client.quant

start = """
import sys
import traceback
from backtrader import indicator
import pandas as pd
from pymongo import MongoClient
from datetime import datetime
import talib
import numpy as np
import math
import backtrader as bt
import backtrader.indicators as btind
import backtrader.feeds as btfeeds
from target.indicator_ma import *
#from IndicatorResult import *  #pmr

objJson = {
    '_id': 'uuidstr',
    'adjustflag': 'adjustflag',
    'code': 'code',
    'start': 'start',
    'end': 'end',
    'startcash': 'startcash',
    'endtcash': 'cerebro.broker.getvalue()',
    'com': 'com',
    'qts': 'qts',
    'basicsData': [],  # 基础数据
    'isbuyArr': [],  # 买入一笔 买入价格
    'issellArr': [],  # 卖出一笔  卖出价格
    'pnlcommArr': [],  # 一笔交易后的盈亏
    'currentCapital': [],  # 资金走势
    'indicatorLines': {},  # 用户所选指标线
    
    'annualReturn':[],  #年度回报
    'Calmar':[],  #卡码率
    'DrawDown':{},  #回撤
    'sharperatio':math.nan,  #夏普比率
    'sqnIndex':{},  #SQN指数
    'TimeReturn':[],  #基于时间范围内回报
}

operationResults = {'_id': '', 'version_id': '', 'operationResults': ""}


# 创建策略继承bt.strategy
class TestStrategy(bt.Strategy):
    params = (
        ('maperiod', 15),
        # 判断是否输出该日志
        ('printlog', False),
    )
"""

end = """
    # 初始化部分指标
    def __init__(self):

        # 调用用户初始化代码
        try:
            self.init()
        except Exception as e:
            exceStr = str(traceback.format_exc())
            print(exceStr)
            operationResults['operationResults'] = exceStr
            add_operationResults()

        # 指标进行注册
        self.indicators = {}
        # 跟踪挂单
        self.order = None
        # 买入价格和手续费
        self.buyprice = None
        self.buycomm = None

        # 初始化注册指标
        for param in indicator_params:
            if param['code'] == 'ma':
                indicator = bt.indicators.MovingAverageSimple(
                    param['data'], period=param['params'][0])
                self.indicators[param['name']] = indicator
                # 修改注册表
                value = indicator_registry[param['name']]
                value['indicator'] = indicator

            elif param['code'] == 'ema':
                indicator = bt.indicators.ExponentialMovingAverage(
                    param['data'], period=param['params'][0])
                self.indicators[param['name']] = indicator
                # 修改注册表
                value = indicator_registry[param['name']]
                value['indicator'] = indicator

            elif param['code'] == 'smma':
                indicator = bt.indicators.SmoothedMovingAverage(
                    param['data'], period=param['params'][0])
                self.indicators[param['name']] = indicator
                # 修改注册表
                value = indicator_registry[param['name']]
                value['indicator'] = indicator

            elif param['code'] == 'wma':
                indicator = bt.indicators.WeightedMovingAverage(
                    param['data'], period=param['params'][0])
                self.indicators[param['name']] = indicator
                # 修改注册表
                value = indicator_registry[param['name']]
                value['indicator'] = indicator

            elif param['code'] == 'dema':
                indicator = bt.indicators.DoubleExponentialMovingAverage(
                    param['data'], period=param['params'][0])
                self.indicators[param['name']] = indicator
                # 修改注册表
                value = indicator_registry[param['name']]
                value['indicator'] = indicator

            elif param['code'] == 'kama':
                indicator = bt.indicators.AdaptiveMovingAverage(
                    param['data'], period=param['params'][0])
                self.indicators[param['name']] = indicator
                # 修改注册表
                value = indicator_registry[param['name']]
                value['indicator'] = indicator

            elif param['code'] == 'zlema':
                indicator = bt.indicators.ZeroLagExponentialMovingAverage(
                    param['data'], period=param['params'][0])
                self.indicators[param['name']] = indicator
                # 修改注册表
                value = indicator_registry[param['name']]
                value['indicator'] = indicator

            elif param['code'] == 'hma':
                indicator = bt.indicators.HullMovingAverage(
                    param['data'], period=param['params'][0])
                self.indicators[param['name']] = indicator
                # 修改注册表
                value = indicator_registry[param['name']]
                value['indicator'] = indicator

            elif param['code'] == 'zlind':
                indicator = bt.indicators.ZeroLagIndicator(
                    param['data'], period=param['params'][0])
                self.indicators[param['name']] = indicator
                # 修改注册表
                value = indicator_registry[param['name']]
                value['indicator'] = indicator

            elif param['code'] == 'dma':
                indicator = bt.indicators.DicksonMovingAverage(
                    param['data'], period=param['params'][0])
                self.indicators[param['name']] = indicator
                # 修改注册表
                value = indicator_registry[param['name']]
                value['indicator'] = indicator
            
            elif param['code'] == 'highest':
                indicator = bt.indicators.Highest(
                    param['data'], period=param['params'][0])
                self.indicators[param['name']] = indicator
                # 修改注册表
                value = indicator_registry[param['name']]
                value['indicator'] = indicator
            
            elif param['code'] == 'lowest':
                indicator = bt.indicators.Lowest(
                    param['data'], period=param['params'][0])
                self.indicators[param['name']] = indicator
                # 修改注册表
                value = indicator_registry[param['name']]
                value['indicator'] = indicator

        # 初始化指标lines对象(用于存储绘图数据)
        for item in indicator_user_arr:
            objJson['indicatorLines'][item] = []

    # 策略核心代码
    def next(self):

        current_date = self.datas[0].datetime.date(0).isoformat()  # 当前日期
        current_capital = self.broker.getvalue()  # 当前剩余资金
        objJson['currentCapital'].append({'date': current_date, 'value': round(current_capital, 2)})  # 保存每一天的资金走势

        # indicator_user_arr：当前用户所选指标列表
        for item in indicator_user_arr:
            # 每一个指标线的值进行保存
            objJson['indicatorLines'][item].append({'date': current_date, 'value': round(self.indicators[item][0], 2)})

        # 调用用户策略
        try:
            self.on_bar()
        except Exception as e:
            exceStr = str(traceback.format_exc())
            print(exceStr)
            operationResults['operationResults'] = exceStr
            add_operationResults()
    
    #买入
    def open_buy(self,size=None):
        self.order = self.buy(size=size)
    #卖出
    def open_sell(self,size=None):
        self.order = self.sell(size=size)
    #平仓
    def open_close(self,size=None):
        self.order = self.close(size=size)
    #取消订单
    def open_cancel(self,size=None):
        self.order = self.cancel(size=size)
    #获取资金
    def get_cash(self):
        return self.broker.getvalue()
    #获取某个时间段内的数据
    # data  获取指定列的数据
    # ago 0：从当前时间点往前取数据（默认为0）
        # 1：从当前时间点的前一天开始
    #size  时间点数据（默认为10）
    def optainData(self,data,ago=0,size = 10):
        return np.array(data.get(ago=ago, size=size))
        
    # 日志输出
    def log(self, txt, dt=None, doprint=False):
        if self.params.printlog or doprint:
            # 记录策略的执行日志
            dt = dt or self.datas[0].datetime.date(0)
            print(f'{dt.isoformat()},{txt}')

    # 订单状态通知，买入卖出都是下单
    def notify_order(self, order):
        # 提交了/接受了,  买/卖订单什么都不做
        if order.status in [order.Submitted, order.Accepted]:
            return
        # 检查一个订单是否完成
        # 注意:当资金不足时，broker会拒绝订单
        if order.status in [order.Completed]:
            if order.isbuy():
                objJson['isbuyArr'].append(
                    {'date': self.datas[0].datetime.date(0).isoformat(), 'value': round(order.executed.price, 4),'size':order.executed.size,'comm':round(order.executed.comm,4)})
                self.buyprice = order.executed.price
                self.buycomm = order.executed.comm
            elif order.issell():
                objJson['issellArr'].append(
                    {'date': self.datas[0].datetime.date(0).isoformat(), 'value': round(order.executed.price, 4),'size':order.executed.size,'comm':round(order.executed.comm,4)})
            self.bar_executed = len(self)
        elif order.status in [order.Canceled, order.Margin, order.Rejected]:
            self.log('订单取消/保证金不足/拒绝', doprint=False)
        # 其他状态记录为：无法挂单
        self.order = None

    # 交易状态通知，一买一卖算交易（交易净利润）
    def notify_trade(self, trade):
        if not trade.isclosed:
            return
        objJson['pnlcommArr'].append(
            {'date': self.datas[0].datetime.date(0).isoformat(), 'value': round(trade.pnlcomm, 2)})

    # 策略结束时，多用于参数调优
    def stop(self):
        # 回测结束存储数据
        # print(objJson)
        self.log('(MA均线： %2d日) 期末总资金 %.2f' %
                 (self.params.maperiod, self.broker.getvalue()), doprint=False)


def main(execution_id, adjustflag, code, start, end='', startcash=1000, com=0.001, qts=100):
    client = MongoClient('47.100.19.231', 27018)
    db = client.admin
    db.authenticate("quant", "Qweasd123")
    db = client.quant
    
    operationResults['_id'] = execution_id
    operationResults['version_id'] = execution_id
    
    
    mycol = db.t_strategy_execution_result
    mydict = {'_id': execution_id}
    s = mycol.find(mydict).count()
    if s > 0:
        return execution_id

    # 创建主控制器
    cerebro = bt.Cerebro()
    # 导入策略参数寻优
    cerebro.addstrategy(TestStrategy)
    # cerebro.optstrategy(TestStrategy,maperiod=range(10, 15))
    # 获取数据
    df = selectSotck(adjustflag, code, start, end)
    df.index = pd.to_datetime(df.date)
    df = df[['open', 'high', 'low', 'close', 'volume']]
    # 将数据加载至回测系统
    data = bt.feeds.PandasData(dataname=df)

    cerebro.adddata(data)
    # broker设置资金、手续费
    cerebro.broker.setcash(startcash)
    cerebro.broker.setcommission(commission=com)
    # 设置买入设置，策略，数量
    cerebro.addsizer(bt.sizers.FixedSize, stake=qts)
    print('期初总资金: %.2f' % cerebro.broker.getvalue())
    cerebro.addanalyzer(bt.analyzers.AnnualReturn, _name='AnnualReturn')  # 年度回报
    cerebro.addanalyzer(bt.analyzers.Calmar, _name='Calmar')  # 卡玛率
    cerebro.addanalyzer(bt.analyzers.DrawDown, _name='DW')  # 回撤
    cerebro.addanalyzer(bt.analyzers.SharpeRatio, _name='SharpeRatio')  # 夏普比率
    cerebro.addanalyzer(bt.analyzers.SQN, _name='SQN')  # SQN指数
    cerebro.addanalyzer(bt.analyzers.TimeReturn, _name='TimeReturn')  # 基于时间范围的回报
    result = cerebro.run(maxcpus=1)
    strat = result[0]

    #年度回报
    for item in (strat.analyzers.AnnualReturn.get_analysis()).items():
        objJson['annualReturn'].append({'date': item[0], 'value': loadabnormal(item[1])})
    #卡码率
    for item in (strat.analyzers.Calmar.get_analysis()).items():
        objJson['Calmar'].append({'date':item[0].strftime('%Y-%m-%d'), 'value': loadabnormal(item[1])})
    #回撤
    dw = strat.analyzers.DW.get_analysis()
    objJson['DrawDown']['DrawDownlen'] = dw['len']
    objJson['DrawDown']['DrawDown'] = loadabnormal(dw['drawdown'])
    objJson['DrawDown']['DrawDownMoney'] = loadabnormal(dw['moneydown'])
    objJson['DrawDown']['maxDrawDownlen'] = dw['max']['len']
    objJson['DrawDown']['maxDrawDown'] = loadabnormal(dw['max']['drawdown'])
    objJson['DrawDown']['maxDrawDownMoney'] = loadabnormal(dw['max']['moneydown'])
    #夏普比率
    SharpeRatio = strat.analyzers.SharpeRatio.get_analysis()
    objJson['sharperatio'] = loadabnormal(SharpeRatio['sharperatio'])
    #SQN指数
    sqn = strat.analyzers.SQN.get_analysis()
    objJson['sqnIndex']['sqn'] = loadabnormal(sqn['sqn'])
    objJson['sqnIndex']['trades'] = loadabnormal(sqn['trades'])
    #基于时间范围内的回报
    for item in (strat.analyzers.TimeReturn.get_analysis()).items():
        objJson['TimeReturn'].append(
            {'date':item[0].strftime('%Y-%m-%d'), 'value': loadabnormal(item[1])})

    print('期末总资金: %.2f' % cerebro.broker.getvalue())
    # cerebro.plot()

    objJson['_id'] = execution_id
    objJson['adjustflag'] = adjustflag
    objJson['code'] = code
    objJson['start'] = start
    objJson['end'] = end
    objJson['startcash'] = startcash
    objJson['endtcash'] = cerebro.broker.getvalue()
    objJson['com'] = com
    objJson['qts'] = qts
    mycol.insert_one(objJson)

    return execution_id


def add_operationResults():
    client = MongoClient('47.100.19.231', 27018)
    db = client.admin
    db.authenticate("quant", "Qweasd123")
    db = client.quant
    operationResultsMycol = db.t_operationResults
    myquery = {'_id': operationResults['_id']}
    operationResultsMycol.delete_many(myquery)
    operationResultsMycol.insert_one(operationResults)
    sys.exit()
    

# 时间处理
def loadDate(str):
    date = datetime.strptime(str, '%Y%m%d').strftime('%Y-%m-%d')
    return (date)

# 异常值处理
def loadabnormal(num):
    if (str(num)=='nan') or (str(num)=='None'):
        return 0
    else:
        return round(num,4)

# 数据查询
def selectSotck(adjustflag, code, startDate, endDate):
    client = MongoClient('47.100.19.231', 27018)
    db = client.admin
    db.authenticate("quant", "Qweasd123")
    db = client.quant
    mycol = db.testKline

    myquery = {
        "adjustflag": adjustflag,  # 复权类型
        "code": code,  # 股票代码
        "date": {'$gt': startDate, '$lt': endDate}  # 时间区间
    }

    mydoc = mycol.find(myquery)
    data_list = []
    for x in mydoc:
        data_list.append(x)
    objJson['basicsData'] = data_list
    fields = ['date', 'open', 'close', 'high', 'low', 'volume', 'code']
    df = pd.DataFrame(data_list, columns=fields)
    df['date'] = df['date'].map(loadDate)
    test_list = ['open', 'close', 'high', 'low', 'volume']
    df[test_list] = df[test_list].astype('float64')
    return df
"""

end1 = """
    # 初始化部分指标
    def __init__(self):

        # 调用用户初始化代码
        try:
            self.init()
        except Exception as e:
            exceStr = str(traceback.format_exc())
            print(exceStr)
            operationResults['operationResults'] = exceStr
            add_operationResults()

        # 指标进行注册
        self.indicators = {}
        # 跟踪挂单
        self.order = None
        # 买入价格和手续费
        self.buyprice = None
        self.buycomm = None

        # 初始化注册指标
        for param in indicator_params:
            if param['code'] == 'ma':
                indicator = bt.indicators.MovingAverageSimple(
                    param['data'], period=param['params'][0])
                self.indicators[param['name']] = indicator
                # 修改注册表
                value = indicator_registry[param['name']]
                value['indicator'] = indicator

            elif param['code'] == 'ema':
                indicator = bt.indicators.ExponentialMovingAverage(
                    param['data'], period=param['params'][0])
                self.indicators[param['name']] = indicator
                # 修改注册表
                value = indicator_registry[param['name']]
                value['indicator'] = indicator

            elif param['code'] == 'smma':
                indicator = bt.indicators.SmoothedMovingAverage(
                    param['data'], period=param['params'][0])
                self.indicators[param['name']] = indicator
                # 修改注册表
                value = indicator_registry[param['name']]
                value['indicator'] = indicator

            elif param['code'] == 'wma':
                indicator = bt.indicators.WeightedMovingAverage(
                    param['data'], period=param['params'][0])
                self.indicators[param['name']] = indicator
                # 修改注册表
                value = indicator_registry[param['name']]
                value['indicator'] = indicator

            elif param['code'] == 'dema':
                indicator = bt.indicators.DoubleExponentialMovingAverage(
                    param['data'], period=param['params'][0])
                self.indicators[param['name']] = indicator
                # 修改注册表
                value = indicator_registry[param['name']]
                value['indicator'] = indicator

            elif param['code'] == 'kama':
                indicator = bt.indicators.AdaptiveMovingAverage(
                    param['data'], period=param['params'][0])
                self.indicators[param['name']] = indicator
                # 修改注册表
                value = indicator_registry[param['name']]
                value['indicator'] = indicator

            elif param['code'] == 'zlema':
                indicator = bt.indicators.ZeroLagExponentialMovingAverage(
                    param['data'], period=param['params'][0])
                self.indicators[param['name']] = indicator
                # 修改注册表
                value = indicator_registry[param['name']]
                value['indicator'] = indicator

            elif param['code'] == 'hma':
                indicator = bt.indicators.HullMovingAverage(
                    param['data'], period=param['params'][0])
                self.indicators[param['name']] = indicator
                # 修改注册表
                value = indicator_registry[param['name']]
                value['indicator'] = indicator

            elif param['code'] == 'zlind':
                indicator = bt.indicators.ZeroLagIndicator(
                    param['data'], period=param['params'][0])
                self.indicators[param['name']] = indicator
                # 修改注册表
                value = indicator_registry[param['name']]
                value['indicator'] = indicator

            elif param['code'] == 'dma':
                indicator = bt.indicators.DicksonMovingAverage(
                    param['data'], period=param['params'][0])
                self.indicators[param['name']] = indicator
                # 修改注册表
                value = indicator_registry[param['name']]
                value['indicator'] = indicator
            
            elif param['code'] == 'highest':
                indicator = bt.indicators.Highest(
                    param['data'], period=param['params'][0])
                self.indicators[param['name']] = indicator
                # 修改注册表
                value = indicator_registry[param['name']]
                value['indicator'] = indicator
            
            elif param['code'] == 'lowest':
                indicator = bt.indicators.Lowest(
                    param['data'], period=param['params'][0])
                self.indicators[param['name']] = indicator
                # 修改注册表
                value = indicator_registry[param['name']]
                value['indicator'] = indicator

        # 初始化指标lines对象(用于存储绘图数据)
        for item in indicator_user_arr:
            objJson['indicatorLines'][item] = []
    # 策略核心代码
    def next(self):

        current_date = self.datas[0].datetime.date(0).isoformat()  # 当前日期
        current_capital = self.broker.getvalue()  # 当前剩余资金
        objJson['currentCapital'].append({'date': current_date, 'value': round(current_capital, 2)})  # 保存每一天的资金走势

        # indicator_user_arr：当前用户所选指标列表
        for item in indicator_user_arr:
            # 每一个指标线的值进行保存
            objJson['indicatorLines'][item].append({'date': current_date, 'value': round(self.indicators[item][0], 2)})

        # 调用用户策略
        try:
            self.on_bar()
        except Exception as e:
            exceStr = str(traceback.format_exc())
            print(exceStr)
            operationResults['operationResults'] = exceStr
            add_operationResults()

    #买入
    def open_buy(self,size=None):
        self.order = self.buy(size=size)
    #卖出
    def open_sell(self,size=None):
        self.order = self.sell(size=size)
    #平仓
    def open_close(self,size=None):
        self.order = self.close(size=size)
    #取消订单
    def open_cancel(self,size=None):
        self.order = self.cancel(size=size)
    #获取资金
    def get_cash(self):
        return self.broker.getvalue()
    #获取某个时间段内的数据
    # data  获取指定列的数据
    # ago 0：从当前时间点往前取数据（默认为0）
        # 1：从当前时间点的前一天开始
    #size  时间点数据（默认为10）
    def optainData(self,data,ago=0,size = 10):
        return np.array(data.get(ago=ago, size=size))

    # 日志输出
    def log(self, txt, dt=None, doprint=False):
        if self.params.printlog or doprint:
            # 记录策略的执行日志
            dt = dt or self.datas[0].datetime.date(0)
            print(f'{dt.isoformat()},{txt}')

    # 订单状态通知，买入卖出都是下单
    def notify_order(self, order):
        # 提交了/接受了,  买/卖订单什么都不做
        if order.status in [order.Submitted, order.Accepted]:
            return
        # 检查一个订单是否完成
        # 注意:当资金不足时，broker会拒绝订单
        if order.status in [order.Completed]:
            if order.isbuy():
                objJson['isbuyArr'].append(
                    {'date': self.datas[0].datetime.date(0).isoformat(), 'value': round(order.executed.price, 4),'size':order.executed.size,'comm':round(order.executed.comm,4)})
                self.buyprice = order.executed.price
                self.buycomm = order.executed.comm
            elif order.issell():
                objJson['issellArr'].append(
                    {'date': self.datas[0].datetime.date(0).isoformat(), 'value': round(order.executed.price, 4),'size':order.executed.size,'comm':round(order.executed.comm,4)})
            self.bar_executed = len(self)
        elif order.status in [order.Canceled, order.Margin, order.Rejected]:
            self.log('订单取消/保证金不足/拒绝', doprint=False)
        # 其他状态记录为：无法挂单
        self.order = None

    # 交易状态通知，一买一卖算交易（交易净利润）
    def notify_trade(self, trade):
        if not trade.isclosed:
            return
        objJson['pnlcommArr'].append(
            {'date': self.datas[0].datetime.date(0).isoformat(), 'value': round(trade.pnlcomm, 2)})

    # 策略结束时，多用于参数调优
    def stop(self):
        # 回测结束存储数据
        # print(objJson)
        self.log('(MA均线： %2d日) 期末总资金 %.2f' %
                 (self.params.maperiod, self.broker.getvalue()), doprint=False)


def main(execution_id, adjustflag, code, start, end='', startcash=1000, com=0.001, qts=100):
    client = MongoClient('47.100.19.231', 27018)
    db = client.admin
    db.authenticate("quant", "Qweasd123")
    db = client.quant

    operationResults['_id'] = execution_id
    operationResults['version_id'] = execution_id



    # 创建主控制器
    cerebro = bt.Cerebro()
    # 导入策略参数寻优
    cerebro.addstrategy(TestStrategy)
    # cerebro.optstrategy(TestStrategy,maperiod=range(10, 15))
    # 获取数据
    df = selectSotck(adjustflag, code, start, end)
    df.index = pd.to_datetime(df.date)
    df = df[['open', 'high', 'low', 'close', 'volume']]
    # 将数据加载至回测系统
    data = bt.feeds.PandasData(dataname=df)

    cerebro.adddata(data)
    # broker设置资金、手续费
    cerebro.broker.setcash(startcash)
    cerebro.broker.setcommission(commission=com)
    # 设置买入设置，策略，数量
    cerebro.addsizer(bt.sizers.FixedSize, stake=qts)
    print('期初总资金: %.2f' % cerebro.broker.getvalue())
    cerebro.addanalyzer(bt.analyzers.AnnualReturn, _name='AnnualReturn')  # 年度回报
    cerebro.addanalyzer(bt.analyzers.Calmar, _name='Calmar')  # 卡玛率
    cerebro.addanalyzer(bt.analyzers.DrawDown, _name='DW')  # 回撤
    cerebro.addanalyzer(bt.analyzers.SharpeRatio, _name='SharpeRatio')  # 夏普比率
    cerebro.addanalyzer(bt.analyzers.SQN, _name='SQN')  # SQN指数
    cerebro.addanalyzer(bt.analyzers.TimeReturn, _name='TimeReturn')  # 基于时间范围的回报
    result = cerebro.run(maxcpus=1)
    strat = result[0]

    #年度回报
    for item in (strat.analyzers.AnnualReturn.get_analysis()).items():
        objJson['annualReturn'].append({'date': item[0], 'value': loadabnormal(item[1])})
    #卡码率
    for item in (strat.analyzers.Calmar.get_analysis()).items():
        objJson['Calmar'].append({'date':item[0].strftime('%Y-%m-%d'), 'value': loadabnormal(item[1])})
    #回撤
    dw = strat.analyzers.DW.get_analysis()
    objJson['DrawDown']['DrawDownlen'] = dw['len']
    objJson['DrawDown']['DrawDown'] = loadabnormal(dw['drawdown'])
    objJson['DrawDown']['DrawDownMoney'] = loadabnormal(dw['moneydown'])
    objJson['DrawDown']['maxDrawDownlen'] = dw['max']['len']
    objJson['DrawDown']['maxDrawDown'] = loadabnormal(dw['max']['drawdown'])
    objJson['DrawDown']['maxDrawDownMoney'] = loadabnormal(dw['max']['moneydown'])
    #夏普比率
    SharpeRatio = strat.analyzers.SharpeRatio.get_analysis()
    objJson['sharperatio'] = loadabnormal(SharpeRatio['sharperatio'])
    #SQN指数
    sqn = strat.analyzers.SQN.get_analysis()
    objJson['sqnIndex']['sqn'] = loadabnormal(sqn['sqn'])
    objJson['sqnIndex']['trades'] = loadabnormal(sqn['trades'])
    #基于时间范围内的回报
    for item in (strat.analyzers.TimeReturn.get_analysis()).items():
        objJson['TimeReturn'].append(
            {'date':item[0].strftime('%Y-%m-%d'), 'value': loadabnormal(item[1])})

    print('期末总资金: %.2f' % cerebro.broker.getvalue())
    # cerebro.plot()

    objJson['_id'] = execution_id
    objJson['adjustflag'] = adjustflag
    objJson['code'] = code
    objJson['start'] = start
    objJson['end'] = end
    objJson['startcash'] = startcash
    objJson['endtcash'] = cerebro.broker.getvalue()
    objJson['com'] = com
    objJson['qts'] = qts


    return execution_id


def add_operationResults():
    client = MongoClient('47.100.19.231', 27018)
    db = client.admin
    db.authenticate("quant", "Qweasd123")
    db = client.quant
    operationResultsMycol = db.t_operationResults
    myquery = {'_id': operationResults['_id']}
    operationResultsMycol.delete_many(myquery)
    operationResultsMycol.insert_one(operationResults)
    sys.exit()


# 时间处理
def loadDate(str):
    date = datetime.strptime(str, '%Y%m%d').strftime('%Y-%m-%d')
    return (date)

# 异常值处理
def loadabnormal(num):
    if (str(num)=='nan') or (str(num)=='None'):
        return 0
    else:
        return round(num,4)

# 数据查询
def selectSotck(adjustflag, code, startDate, endDate):
    client = MongoClient('47.100.19.231', 27018)
    db = client.admin
    db.authenticate("quant", "Qweasd123")
    db = client.quant
    mycol = db.testKline

    myquery = {
        "adjustflag": adjustflag,  # 复权类型
        "code": code,  # 股票代码
        "date": {'$gt': startDate, '$lt': endDate}  # 时间区间
    }

    mydoc = mycol.find(myquery)
    data_list = []
    for x in mydoc:
        data_list.append(x)
    objJson['basicsData'] = data_list
    fields = ['date', 'open', 'close', 'high', 'low', 'volume', 'code']
    df = pd.DataFrame(data_list, columns=fields)
    df['date'] = df['date'].map(loadDate)
    test_list = ['open', 'close', 'high', 'low', 'volume']
    df[test_list] = df[test_list].astype('float64')
    return df
"""

mainstr = """
main("""


# 添加 用户_策略，基础信息
@app.route('/add/user/strategy/info', methods=['GET', 'POST'])
def add_user_strategy_info():
    # 策略_ID
    data = request.get_json(silent=True)
    strategy_id = str(uuid.uuid1())
    user_id = data['user_id']
    strategy_name = data['strategy_name']
    # version = data['version']
    created_time = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(time.time()))
    # 验证 同一策略用户id和策略名称不可重复
    mydict = {'user_id': user_id, 'strategy_name': strategy_name}
    mycol = db.t_user_strategy_info
    s = mycol.find(mydict).count()
    if s > 0:
        return formatting_data(200, msg='success', data={'param': '版本重复'})

    # 添加信息
    # mydict = {'_id': strategy_id, "strategy_id": strategy_id, "user_id": user_id, "strategy_name": strategy_name, 'version': version,
    #           'created_time': created_time}
    # 添加信息
    mydict = {'_id': strategy_id, "strategy_id": strategy_id, "user_id": user_id, "strategy_name": strategy_name,
              'created_time': created_time}
    # 表
    mycol = db.t_user_strategy_info
    # 添加
    x = mycol.insert_one(mydict)
    # 返回添加id
    a = mycol.find(mydict).count()
    return formatting_data(200, msg='success', data={'param': str(a)})


# 查询 用户_策略，基础信息
@app.route('/get/user/strategy/info/by_id', methods=['GET', 'POST'])
def get_user_strategy_info_by_id():
    # 策略_ID
    data = request.get_json(silent=True)
    strategy_id = data['strategy_id']
    # 条件
    mydict = {"strategy_id": strategy_id}
    # 表
    mycol = db.t_user_strategy_info
    data = mycol.find(mydict)
    dataList = []
    for x in data:
        dataList.append(x)
    return formatting_data(200, msg='success', data={'param': dataList})


# 查询 用户_策略，基础信息
@app.route('/get/user/strategy/info', methods=['GET', 'POST'])
def get_user_strategy_info():
    # 策略_ID
    data = request.get_json(silent=True)
    user_id = data['user_id']
    pageNum = data['pageNum']
    pageSize = data['pageSize']
    strategy_name = data['strategy_name']

    if pageNum == 1:
        pageNum = 0
    else:
        pageNum = pageNum * 10 - 11
    if strategy_name == '无':
        # 条件
        mydict = {"user_id": user_id}
        # 表
        mycol = db.t_user_strategy_info
        # 查询总条数
        total = mycol.find(mydict).count()
        # 分页查询
        data = mycol.find(mydict).skip(pageNum).limit(pageSize)
    else:
        # 条件
        mydict = {"user_id": user_id, 'strategy_name': {'$regex': strategy_name}}
        # 表
        mycol = db.t_user_strategy_info
        # 查询总条数
        total = mycol.find(mydict).count()
        # 分页查询
        data = mycol.find(mydict).skip(pageNum).limit(pageSize)

    dataList = []

    for x in data:
        print(x)
        dataList.append(x)
    return formatting_data(200, msg='success', data={'param': dataList, "total": total})


# 删除 用户_策略，基础信息
@app.route('/del/user/strategy/info', methods=['GET', 'POST'])
def del_user_strategy_info():
    # 策略_ID
    data = request.get_json(silent=True)
    strategy_id = data['strategy_id']
    mydict = {"strategy_id": strategy_id}
    print(mydict)
    mycol = db.t_user_strategy_info

    x = mycol.delete_many(mydict)
    return formatting_data(200, msg='success', data={'param': x.deleted_count})


# 需改 用户_策略，基础信息
@app.route('/up/user/strategy/info', methods=['GET', 'POST'])
def up_user_strategy_info():
    # # 策略_ID
    data = request.get_json(silent=True)
    strategy_id = data['strategy_id']
    user_id = data['user_id']
    strategy_name = data['strategy_name']
    created_time = data['created_time']
    # 验证 同一策略用户id和策略名称不可重复
    mydict = {'strategy_id': {'$ne': strategy_id}, 'user_id': user_id, 'strategy_name': strategy_name}
    mycol = db.t_user_strategy_info
    s = mycol.find(mydict).count()
    if s > 0:
        return formatting_data(200, msg='success', data={'param': '版本重复'})
    # 修改信息
    mydict = {"strategy_id": strategy_id}
    newvalues = {"$set": {'user_id': user_id, 'strategy_name': strategy_name, 'created_time': created_time}}
    # 表
    mycol = db.t_user_strategy_info
    # 添加
    x = mycol.update_one(mydict, newvalues)

    return formatting_data(200, msg='success', data={'param': x.modified_count})


# 添加 策略_版本，信息
@app.route('/add/strategy/version', methods=['GET', 'POST'])
def add_strategy_version():
    # 策略_ID
    data = request.get_json(silent=True)
    # 版本_id
    version_id = str(uuid.uuid1())
    # 策略_id
    strategy_id = data['strategy_id']
    # 版本
    version = data['version']
    # 验证 同一策略_id和版本不可重复

    # 条件
    mydict = {'strategy_id': strategy_id, 'version': version}
    mycol = db.t_strategy_version
    s = mycol.find(mydict).count()
    if s > 0:
        return formatting_data(200, msg='success', data={'param': '版本重复'})

    # 创建时间
    created_time = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(time.time()))
    # 开始时间
    start_date = data['start_date']
    # 结束时间
    end_date = data['end_date']
    # 账户金额
    account_money = data['account_money']
    # 佣金倍率
    commission_rate = data['commission_rate']
    # 股票代码
    stock_code = data['stock_code']
    # 复权类型
    adjustflag = data['adjustflag']
    # 用户代码
    strategy_code = data['strategy_code']
    # 添加信息
    mydict = {'_id': version_id, 'version_id': version_id, 'strategy_id': strategy_id, 'version': version,
              'strategy_code': strategy_code,
              'created_time': created_time, 'status': 0, 'start_date': start_date,
              'end_date': end_date, 'account_money': account_money, 'commission_rate': commission_rate,
              'stock_code': stock_code, 'adjustflag': adjustflag}
    # 添加
    x = mycol.insert_one(mydict)
    # 查询
    a = mycol.find(mydict).count()
    return formatting_data(200, msg='success', data={'param': str(a), 'version_id': version_id})


# 查询 策略_版本，信息 _id
@app.route('/get/strategy/version/by_id', methods=['GET', 'POST'])
def get_strategy_version_by_id():
    # 策略_ID
    data = request.get_json(silent=True)
    version_id = data['version_id']
    # 条件
    mydict = {"version_id": version_id}
    # 表
    mycol = db.t_strategy_version
    # 查询总条数
    # 分页查询
    data = mycol.find(mydict)
    # 将返回值转list
    dataList = []
    for a in data:
        dataList.append(a)
        print(a)
    return formatting_data(200, msg='success', data={'param': dataList})


# 查询 策略_版本，信息
@app.route('/get/strategy/version', methods=['GET', 'POST'])
def get_strategy_version():
    # 策略_ID
    data = request.get_json(silent=True)
    strategy_id = data['strategy_id']
    pageNum = data['pageNum']
    pageSize = data['pageSize']
    version_id = data['version_id']

    # 条件
    if version_id == '无':
        mydict = {"strategy_id": strategy_id, }
    else:
        mydict = {"strategy_id": strategy_id, 'version_id': version_id}

    # 分页
    if pageNum == 1:
        pageNum = 0
    else:
        pageNum = pageNum * 10 - 11
    # 表
    mycol = db.t_strategy_version
    # 查询总条数
    x = mycol.find(mydict).count()
    # 分页查询
    data = mycol.find(mydict).skip(pageNum).limit(pageSize).sort("created_time", -1)
    # 将返回值转list
    dataList = []
    for a in data:
        dataList.append(a)
    return formatting_data(200, msg='success', data={'param': dataList, "total": x})


# 删除 用户_策略，基础信息
@app.route('/del/strategy/version', methods=['GET', 'POST'])
def del_strategy_version():
    # 策略_ID
    # 策略_ID
    data = request.get_json(silent=True)
    version_id = data['version_id']
    mydict = {"version_id": version_id}
    mycol = db.t_strategy_version
    x = mycol.delete_many(mydict)
    return formatting_data(200, msg='success', data={'param': x.deleted_count})


# 需改 用户_策略，基础信息
@app.route('/up/strategy/version', methods=['GET', 'POST'])
def up_strategy_version():
    # # 策略_ID
    data = request.get_json(silent=True)
    version_id = data['version_id']
    version = data['version']
    strategy_code = data['strategy_code']
    strategy_id = data['strategy_id']
    # 验证 同一策略_id和版本不可重复
    mydict = {'version_id': {'$ne': version_id}, 'strategy_id': strategy_id, 'version': version}
    mycol = db.t_strategy_version
    s = mycol.find(mydict).count()
    if s > 0:
        return formatting_data(200, msg='success', data={'param': '版本重复'})
    # 创建时间
    created_time = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(time.time()))
    # 开始时间
    start_date = data['start_date']
    # 结束时间
    end_date = data['end_date']
    # 账户金额
    account_money = data['account_money']
    # 佣金倍率
    commission_rate = data['commission_rate']
    # 股票代码
    stock_code = data['stock_code']
    # 复权类型
    adjustflag = data['adjustflag']
    # 用户代码
    strategy_code = data['strategy_code']

    # 修改信息
    mydict = {"version_id": version_id}
    newvalues = {"$set": {'version': version, 'strategy_code': strategy_code,
                          'created_time': created_time, 'start_date': start_date, 'end_date': end_date,
                          'account_money': account_money,
                          'commission_rate': commission_rate, 'stock_code': stock_code, 'adjustflag': adjustflag}}
    # 表
    # 添加
    x = mycol.update_one(mydict, newvalues)
    return formatting_data(200, msg='success', data={'param': x.modified_count})


# 查询 策略执行信息
@app.route('/get/strategy/execution/info', methods=['GET', 'POST'])
def get_strategy_execution_info():
    data = request.get_json(silent=True)
    # 版本id
    version_id = data['version_id']
    # 执行名称
    execution_name = data['execution_name']
    pageNum = data['pageNum']
    pageSize = data['pageSize']
    if pageNum == 1:
        pageNum = 0
    else:
        pageNum = pageNum * 10 - 11
    if execution_name == '无':
        # 条件
        mydict = {"version_id": version_id}
        # 表
        mycol = db.t_strategy_execution_info
        # 查询总条数
        total = mycol.find(mydict).count()
        # 分页查询
        data = mycol.find(mydict).skip(pageNum).limit(pageSize)
    else:
        # 条件
        mydict = {"version_id": version_id, 'execution_name': {'$regex': execution_name}}
        # 表
        mycol = db.t_strategy_execution_info
        # 查询总条数
        total = mycol.find(mydict).count()
        # 分页查询
        data = mycol.find(mydict).skip(pageNum).limit(pageSize)
    dataList = []
    for x in data:
        print(x)
        dataList.append(x)
    return formatting_data(200, msg='success', data={'param': dataList, "total": total})


# 查询 策略执行信息
@app.route('/get/strategy/execution/info_by_id', methods=['GET', 'POST'])
def get_strategy_execution_info_by_id():
    data = request.get_json(silent=True)
    # 执行_id
    execution_id = data['execution_id']
    # 表
    mycol = db.t_strategy_execution_info
    # 查询
    mydict = {"execution_id": execution_id}
    data = mycol.find(mydict)
    dataList = []
    for x in data:
        dataList.append(x)
    return formatting_data(200, msg='success', data={'param': dataList})


# 添加 策略执行信息
@app.route('/add/strategy/execution/info', methods=['GET', 'POST'])
def add_strategy_execution_info():
    data = request.get_json(silent=True)
    # 执行_id
    execution_id = str(uuid.uuid1())
    # 版本id
    version_id = data['version_id']
    # # 策略_ID
    # strategy_id = data['strategy_id']
    # 执行名称
    execution_name = data['execution_name']
    # 创建时间
    created_time = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(time.time()))
    # 开始时间
    start_date = data['start_date']
    # 结束时间
    end_date = data['end_date']
    # 账户金额
    account_money = data['account_money']
    # 佣金倍率
    commission_rate = data['commission_rate']
    # 股票代码
    stock_code = data['stock_code']
    # 复权类型
    adjustflag = data['adjustflag']

    # 验证
    # 条件
    mydict = {'version_id': version_id, 'execution_name': execution_name}
    mycol = db.t_strategy_execution_info
    s = mycol.find(mydict).count()
    if s > 0:
        return formatting_data(200, msg='success', data={'param': '名称重复'})

    # # 查看 策略_版本代码
    # # 条件
    # mydict = {"version_id": version_id}
    # # 表
    # mycol = db.t_strategy_version
    # # 查询总条数
    # # 分页查询
    # data = mycol.find(mydict)
    # # 将返回值转list
    # strategy_code = ''
    # for a in data:
    #     print(a['strategy_code'])
    #     strategy_code = a['strategy_code']
    #
    # # 策略代码
    # # 生成py文件
    # content = start+strategy_code+end
    # f = open(execution_id+'.py', 'w', encoding='utf-8')
    # f.write(content)
    # f.close()

    # 添加数据
    # 表
    mycol = db.t_strategy_execution_info
    # 信息
    mydict = {"_id": execution_id, 'execution_id': execution_id, 'file_name': execution_id + '.py',
              'version_id': version_id,
              'execution_name': execution_name, 'created_time': created_time, 'start_date': start_date, 'status': 0,
              'end_date': end_date, 'account_money': account_money, 'commission_rate': commission_rate,
              'stock_code': stock_code, 'adjustflag': adjustflag}
    # 添加
    x = mycol.insert_one(mydict)
    # 查询
    a = mycol.find(mydict).count()
    return formatting_data(200, msg='success', data={'param': str(a)})


# 更新 策略执行信息
@app.route('/up/strategy/execution/info', methods=['GET', 'POST'])
def up_strategy_execution_info():
    data = request.get_json(silent=True)
    # 执行_id
    execution_id = data['execution_id']
    # 版本id
    version_id = data['version_id']
    # 执行名称
    execution_name = data['execution_name']
    # 开始时间
    start_date = data['start_date']
    # 结束时间
    end_date = data['end_date']
    # 账户金额
    account_money = data['account_money']
    # 佣金倍率
    commission_rate = data['commission_rate']
    # 股票代码
    stock_code = data['stock_code']
    # 复权来信
    adjustflag = data['adjustflag']

    # 验证
    # 条件
    mydict = {'execution_id': {'$ne': execution_id}, 'version_id': version_id, 'execution_name': execution_name}
    mycol = db.t_strategy_execution_info
    s = mycol.find(mydict).count()
    if s > 0:
        return formatting_data(200, msg='success', data={'param': '名称重复'})
    # 表
    mycol = db.t_strategy_execution_info
    # 修改信息
    mydict = {"execution_id": execution_id}
    newvalues = {"$set": {'execution_id': execution_id, 'version_id': version_id,
                          'execution_name': execution_name, 'start_date': start_date,
                          'end_date': end_date, 'account_money': account_money, 'commission_rate': commission_rate,
                          'stock_code': stock_code, 'adjustflag': adjustflag}}
    x = mycol.update_one(mydict, newvalues)
    return formatting_data(200, msg='success', data={'param': x.modified_count})


# 删除 策略执行信息
@app.route('/del/strategy/execution/info', methods=['GET', 'POST'])
def del_strategy_execution_info():
    data = request.get_json(silent=True)
    # 执行_id
    execution_id = data['execution_id']
    mycol = db.t_strategy_execution_info
    mydict = {'execution_id': execution_id}
    x = mycol.delete_many(mydict)
    return formatting_data(200, msg='success', data={'param': x.deleted_count})


# 编译 策略
@app.route('/compile/strategy_code', methods=['GET', 'POST'])
def compile_strategy_code():
    data = request.get_json(silent=True)
    # 版本id
    version_id = data['version_id']
    # 开始时间
    start_date = data['start_date']
    # 结束时间
    end_date = data['end_date']
    # 账户金额
    account_money = data['account_money']
    # 佣金倍率
    commission_rate = data['commission_rate']
    # 股票代码
    stock_code = data['stock_code']
    # 复权来信
    adjustflag = data['adjustflag']

    # 策略_ID
    strategy_id = data['strategy_id']

    # 需改状态值
    # 条件
    mydict = {'version_id': version_id}
    # 表
    mycol = db.t_strategy_version
    # 修改信息
    mydict = {"version_id": version_id}
    newvalues = {"$set": {'status': 1}}
    x = mycol.update_one(mydict, newvalues)

    # (policy_id, code, start, end, startcash=10000, com=0.001, qts=500,):
    e = mainstr + '\'' + version_id + '\',\'' + adjustflag + '\',\'' + stock_code + '\',\'' + start_date + '\',\'' + end_date + '\',' + account_money + ',' + commission_rate + ')'

    # 查看 策略_版本代码
    # 表
    mycol = db.t_strategy_version
    # 修改信息
    mydict = {"version_id": version_id}
    newvalues = {"$set": {'status': 1}}
    x = mycol.update_one(mydict, newvalues)

    # 查询总条数
    # 分页查询
    data = mycol.find(mydict)
    # 将返回值转list
    strategy_code = ''
    for a in data:
        strategy_code = a['strategy_code']
    # 策略代码
    # 生成py文件
    content = start + re.sub('\n', '\n    ', strategy_code) + end + e
    try:
        c = compile(content, '', 'exec')
    except Exception as exce:
        return formatting_data(500, msg='FAIL', data=str(traceback.format_exc()))
    f = open(version_id + '.py', 'w', encoding='utf-8')
    f.write(content)
    f.close()
    return formatting_data(200, msg='success', data={'param': version_id})


# 编译 策略
@app.route('/compile/code', methods=['GET', 'POST'])
def compile_code():
    try:
        data = request.get_json(silent=True)
        # 版本id
        version_id = 'tsetcode'
        # 开始时间
        start_date = data['start_date']
        # 结束时间
        end_date = data['end_date']
        # 账户金额
        account_money = data['account_money']
        # 佣金倍率
        commission_rate = data['commission_rate']
        # 股票代码
        stock_code = data['stock_code']
        # 复权来信
        adjustflag = data['adjustflag']

        strategy_code = data['strategy_code']

        # (policy_id, code, start, end, startcash=10000, com=0.001, qts=500,):
        e = mainstr + '\'' + version_id + '\',\'' + adjustflag + '\',\'' + stock_code + '\',\'' + start_date + '\',\'' + end_date + '\',' + account_money + ',' + commission_rate + ')'

        content = start + re.sub('\n', '\n    ', strategy_code) + end1 + e
        f = open(version_id + '.py', 'w', encoding='utf-8')
        f.write(content)
        f.close()
        c = compile(content, '', 'exec')

    except Exception as exce:
        return formatting_data(500, msg='FAIL', data=str(traceback.format_exc()))

    status = os.system(version_id + '.py')

    client = MongoClient('47.100.19.231', 27018)
    db = client.admin
    db.authenticate("quant", "Qweasd123")
    db = client.quant
    operationResultsMycol = db.t_operationResults
    myquery = {'_id': version_id}
    mydoc = operationResultsMycol.find(myquery)
    for x in mydoc:
        print(x['operationResults'])
        operationResultsMycol.delete_many(myquery)
        return formatting_data(500, msg='FAIL', data=x['operationResults'])

    return formatting_data(200, msg='success', data=status)


# run 策略
@app.route('/run/trategy/code', methods=['GET', 'POST'])
def run_strategy_code():
    data = request.get_json(silent=True)
    # 执行_id
    version_id = data['version_id']

    status = os.system(version_id + '.py')

    client = MongoClient('47.100.19.231', 27018)
    db = client.admin
    db.authenticate("quant", "Qweasd123")
    db = client.quant
    operationResultsMycol = db.t_operationResults
    myquery = {'_id': version_id}
    mydoc = operationResultsMycol.find(myquery)

    for x in mydoc:
        operationResultsMycol.delete_many(myquery)
        return formatting_data(500, msg='FAIL', data=x['operationResults'])
    return formatting_data(200, msg='success', data={'param': version_id, 'status': status})


# 编译 策略
@app.route('/git/strategy/execution/result', methods=['GET', 'POST'])
def git_strategy_execution_result():
    data = request.get_json(silent=True)
    # 执行_id
    version_id = data['version_id']
    # 查询
    mydict = {"_id": version_id}
    mycol = db.t_strategy_execution_result
    data = mycol.find(mydict)
    dataList = []
    for x in data:
        dataList.append(x)
    l = len(dataList)
    if l > 0:
        return formatting_data(200, msg='success', data={'param': dataList})
    else:
        return formatting_data(200, msg='success', data={'param': '未执行'})


@app.route('/get/shares/info', methods=['GET', 'POST'])
def get_shares_info():
    # 连接数据库
    connect = MySQLdb.connect("47.100.19.231", "fcdb", "fcdb", "doquant", charset='utf8',
                              cursorclass=MySQLdb.cursors.DictCursor)
    data = request.get_json(silent=True)
    policy_id = data['code']
    data = (policy_id,)
    sql = "SELECT * FROM t_shares_info where code like '%%%s%%'  LIMIT 0,10"
    sql = sql % data
    # 获取游标
    cursor = connect.cursor()
    cursor.execute(sql)
    date = cursor.fetchall()
    return formatting_data(200, msg='success', data={'param': date})


@app.route('/login', methods=['GET', 'POST'])
def login():
    # 连接数据库
    connect = MySQLdb.connect("47.100.19.231", "fcdb", "fcdb", "doquant", charset='utf8',
                              cursorclass=MySQLdb.cursors.DictCursor)
    data = request.get_json(silent=True)
    username = data['username']
    password = data['password']

    data = (username, password)
    sql = "SELECT COUNT(*) as a FROM sys_user where user_name = '%s'  and password='%s' "
    sql = sql % data

    sql1 = " SELECT user_id,user_name,PASSWORD FROM sys_user where user_name = '%s'  and password='%s' "

    sql1 = sql1 % data

    # 获取游标
    cursor = connect.cursor()
    cursor.execute(sql)
    date = cursor.fetchall()

    cursor.execute(sql1)
    date1 = cursor.fetchall()
    if int(date[0]['a']) > 0:
        user_id = str(date1[0]['user_id'])
        strategy_id = str(uuid.uuid1())
        token = user_id + ',' + strategy_id
        # 创建时间 优化格式化化版本
        CREATED_TIME = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(time.time()))
        datatoken = (strategy_id, user_id, token, CREATED_TIME)
        sql = "INSERT INTO sys_token (token_id, user_id, token, CREATED_TIME) VALUES ('%s', %s, '%s', '%s')"
        datatoken = sql % datatoken
        print(datatoken)
        try:
            cursor.execute(datatoken)
            connect.commit()
        except:
            connect.rollback()

        connect.close()
        return jsonify({"code": 200, "msg": '操作成功',
                        "token": token})
    return formatting_data(500, msg='登陆失败', data={'param': ''})



@app.route('/getInfo', methods=['GET', 'POST'])
def getInfo1():
    token = request.headers.get("Authorization")
    key, key2 = token.split(',')
    user_id = key.split('Bearer ')

    # 连接数据库
    connect = MySQLdb.connect("47.100.19.231", "fcdb", "fcdb", "doquant", charset='utf8',
                              cursorclass=MySQLdb.cursors.DictCursor)
    data = request.get_json(silent=True)

    user_id = user_id[1]
    data = (user_id[1])
    sql = "SELECT user_id as userId, user_name AS userName ,nick_name AS nickName,create_by AS createBy," \
          "create_time AS createTime FROM sys_user WHERE user_id = '%s' "
    sql = sql % data

    # 获取游标
    cursor = connect.cursor()
    cursor.execute(sql)
    date = cursor.fetchall()
    if len(date) > 0:
        return jsonify({"msg": "操作成功", "code": 200, "user": {"searchValue": None,
                                "createBy": date[0]['createBy'], "createTime": date[0]['createTime'],
                                "updateBy": None, "updateTime": None, "remark": None, "params": {},
                                "userId": date[0]['userId'], "deptId": None,"userName": date[0]['userName'],
                                "nickName": date[0]['nickName'], "email": "", "phonenumber": "",
                                "sex": "0", "avatar": "", "salt": None, "status": "0", "delFlag": "0",
                                "loginIp": "",  "loginDate": None, "dept": None,
                                "roles": [{"searchValue": None, "createBy": None, "createTime": None,
                                            "updateBy": None,  "updateTime": None, "remark": None, "params": {},
                                            "roleId": 2, "roleName": "None", "roleKey": "None", "roleSort": "2",
                                            "dataScope": "2", "menuCheckStrictly": False, "deptCheckStrictly": False,
                                            "status": "0", "delFlag": None, "flag": False, "menuIds": None,
                                            "deptIds": None, "admin": False}],
                                "roleIds": None, "postIds": None, "admin": False}})
    return formatting_data(500, msg='失败', data='')


#
@app.route('/getRouters', methods=['GET', 'POST'])
def getRouters1():
    token = request.headers.get("Authorization")
    # msg = verificationtoken(token)
    if 0==0:
        key, key2 = token.split(',')
        user_id = key.split('Bearer ')
        # 连接数据库
        connect = MySQLdb.connect("47.100.19.231", "fcdb", "fcdb", "doquant", charset='utf8',
                                  cursorclass=MySQLdb.cursors.DictCursor)
        user_id = user_id[1]
        data = (user_id)
        sql = "SELECT user_id as userId, user_name AS userName ,nick_name AS nickName,create_by AS createBy," \
              "create_time AS createTime FROM sys_user WHERE user_id = '%s' "
        sql = sql % data
        # 获取游标
        cursor = connect.cursor()
        cursor.execute(sql)
        date = cursor.fetchall()
        if len(date) > 0:
            return jsonify({"msg": "操作成功", "code": 200, "data": [
                {"name": "Backtest", "path": "/backtest", "hidden": False, "redirect": "noRedirect", "component": "Layout",
                 "alwaysShow": True, "meta": {"title": "回测平台", "icon": "guide", "noCache": False}, "children": [
                    {"name": "StrategyList", "path": "strategyList", "hidden": False, "component": "ParentView",
                     "meta": {"title": "策略列表", "icon": "button", "noCache": False}}]}]})
        return formatting_data(500, msg='失败', data='')
    if msg == 1:
        return formatting_data(500, msg='token过期', data='')
    if msg == -1:
        return formatting_data(500, msg='exes', data='')



@app.route('/logout', methods=['GET', 'POST'])
def logout():
    token = request.headers.get("Authorization")
    print(token)

    key, key2 = token.split(',')
    # 连接数据库
    db = MySQLdb.connect("47.100.19.231", "fcdb", "fcdb", "doquant", charset='utf8',
                              cursorclass=MySQLdb.cursors.DictCursor)
    # 使用cursor()方法获取操作游标
    cursor = db.cursor()

    # SQL 删除语句
    sql = "DELETE FROM sys_token WHERE token_id = '%s' " % key2
    try:
       # 执行SQL语句
       cursor.execute(sql)
       # 提交修改
       db.commit()
    except:
       # 发生错误时回滚
       db.rollback()
    # 关闭连接

    return ''



def verificationtoken(token):
    key, key2 = token.split(',')
    db = MySQLdb.connect("47.100.19.231", "fcdb", "fcdb", "doquant", charset='utf8',
                              cursorclass=MySQLdb.cursors.DictCursor)
    # 使用cursor()方法获取操作游标
    cursor = db.cursor()
    # SQL 查询语句
    sql = "SELECT user_id, token, CREATED_TIME FROM sys_token WHERE token_id=%s" % key2
    try:
        # 执行SQL语句
        cursor.execute(sql)
        # 获取所有记录列表
        results = cursor.fetchall()
        print(results)
        for row in results:
            startTime1 = row['CREATED_TIME']
            endTime1 = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(time.time()))
            # 计算分钟数
            startTime2 = datetime.datetime.strptime(startTime1, "%Y-%m-%d %H:%M:%S")
            endTime2 = datetime.datetime.strptime(endTime1, "%Y-%m-%d %H:%M:%S")
            seconds = (endTime2 - startTime2).seconds
            # 来获取时间差中的秒数。注意，seconds获得的秒只是时间差中的小时、分钟和秒部分的和，并没有包含时间差的天数（既是两个时间点不是同一天，失效）
            total_seconds = (endTime2 - startTime2).total_seconds()
            # 来获取准确的时间差，并将时间差转换为秒
            mins = total_seconds / 60
            print(mins)
            if mins < 30:
                db.close()
                return 0
            else:
                sql = "DELETE FROM sys_token WHERE token_id = %s" % key2
                try:
                    # 执行SQL语句
                    cursor.execute(sql)
                    # 提交修改
                    db.commit()
                except:
                    # 发生错误时回滚
                    db.rollback()
                    return -1
                    # 关闭连接
                db.close()
                return 1
    except:
        return -1



@app.route('/user_id', methods=['GET', 'POST'])
def userid():
    token = request.headers.get("Cookie")
    print(token)
    key = token.split('%')
    if len(key) == 2:
        user_id = key[0].split('Admin-Token=')
        return formatting_data(200, msg='', data=user_id[1])
    return formatting_data(500, msg='未登陆', data='')






#
# @app.route('/add/policy/info',methods=['GET', 'POST'])
# def world1():
#     data = request.get_json(silent=True)
#     user_id = data['userId']
#     policy_name = data['policyName']
#     # print(policy_name)
#     # print(user_id)
#     # 时间 优化格式化化版本
#     created_time = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(time.time()))
#     sql = "INSERT INTO t_user_policy_info (policy_name, created_time, user_id) VALUES ( '%s', '%s', '%s' )"
#     data = (policy_name, created_time, user_id)
#     asd = DB.DBHelper()
#     msg = asd.add_date(sql, data)
#     return formatting_data(200, msg='success', data={'param': msg})
#
#
#
#
#
#
#
#
# @app.route('/del/policy/info',methods=['GET', 'POST'])
# def del_policy():
#     data = request.get_json(silent=True)
#     policy_id = data['policy_id']
#
#     print(policy_id)
#
#     sql = "DELETE FROM t_user_policy_info WHERE policy_id = %s"
#     data = (policy_id,)
#     asd = DB.DBHelper()
#     msg = asd.del_date(sql, data)
#     return formatting_data(200, msg='success', data={'param': msg})
#
#
#
#
#
#
# @app.route('/get/policy/info',methods=['GET', 'POST'])
# def get_policy_info():
#     data = request.get_json(silent=True)
#     user_id = data['userId']
#     pageNum = data['pageNum']
#     pageSize = data['pageSize']
#     asd = DB.DBHelper()
#     data = (user_id,)
#     sql1 = "SELECT count(*) FROM t_user_policy_info where user_id= '%s'"
#     sql1 = sql1 % data
#     total = asd.git_date(sql1)
#
#     sql = "SELECT * FROM t_user_policy_info WHERE user_id= '%s' limit %s,%s"
#     sql = "SELECT * FROM t_user_policy_info WHERE user_id= '%s'"
#     data = (user_id, pageNum, pageSize)
#     data = (user_id,)
#     sql = sql % data
#     print(total)
#     print(sql)
#     param = asd.git_date(sql)
#     print(param)
#
#
#     return formatting_data(200, msg='success', data={'param': param, "total": total[0]['count(*)']})
#
#
# @app.route('/get/back/test/info',methods=['GET', 'POST'])
# def get_back_test_infor():
#     data = request.get_json(silent=True)
#     policy_id = data['policy_id']
#     myquery = {"policy_id": policy_id}
#     print('--------------------1')
#     client = MongoClient('47.100.19.231', 27018)
#     db = client.admin
#     db.authenticate("quant", "Qweasd123")
#     db = client.quant
#     mycol = db.backTestInfor
#     # 查询
#     mydoc = mycol.find(myquery)
#     print('--------------------2')
#     return formatting_data(200, msg='success', data={'param': mydoc[0],})
#
#
#
#
#
#
# @app.route('/get/policy/code',methods=['GET', 'POST'])
# def get_policy_code():
#     data = request.get_json(silent=True)
#     policy_id = data['policy_id']
#     asd = DB.DBHelper()
#     data = (policy_id,)
#     sql = "SELECT * FROM t_user_backtest_code where policy_id= %s"
#
#     sql = sql % data
#     print(sql)
#     param = asd.git_date(sql)
#     print(param)
#     return formatting_data(200, msg='success', data={'param': param})
#
#
#
# @app.route('/add/policy/code',methods=['GET', 'POST'])
# def add_policy_code():
#     data = request.get_json(silent=True)
#     # id
#     policy_id = data['policy_id']
#     print(policy_id)
#     #开始时间
#     startDate = data["start_date"]
#     print(startDate)
#     # 结束时间
#     endDate = data["end_date"]
#     print(endDate)
#     # 账户金额
#     accountMoney = data["account_money"]
#     print(accountMoney)
#     # 佣金倍率
#     commissionRate = data["commission_rate"]
#     print(commissionRate)
#     # 策略
#     on_bar = data["on_bar"]
#     on_bar = '"'+on_bar+'"'
#     # 股票代码
#     stock_code = data["stock_code"]
#     print(stock_code)
#     # 创建时间 优化格式化化版本
#     created_time = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(time.time()))
#
#     data = (policy_id, startDate, endDate, accountMoney, commissionRate, on_bar, stock_code, created_time)
#     sql = "INSERT INTO t_user_backtest_code (policy_id, start_date, end_date, account_money, commission_rate, on_bar, stock_code, created_time) VALUES ( %s, '%s', '%s', '%s', '%s', %s, '%s', '%s' )"
#     sql1 = sql % data
#     print(sql1)
#     asd = DB.DBHelper()
#     msg = asd.add_date(sql, data)
#     return formatting_data(200, msg='success', data={'param': msg})
#
#
#
#
#
#
#
#
# @app.route('/start/policy/code',methods=['GET', 'POST'])
# def start_policy_code():
#     data = request.get_json(silent=True)
#     # id
#     policy_id = data['policy_id']
#
#     #开始时间
#     startDate = data["start_date"]
#
#     # 结束时间
#     endDate = data["end_date"]
#
#     # 账户金额
#     accountMoney = data["account_money"]
#
#     # 佣金倍率
#     commissionRate = data["commission_rate"]
#
#     # 策略
#     on_bar = data["on_bar"]
#     # 股票代码
#     stock_code = data["stock_code"]
#
#     content = start+on_bar+end
#
#     f = open('E:/Users/Administrator/PycharmProjects/flaskProject\MyStrategy.py', 'w', encoding='utf-8')
#     f.write(content)
#     f.close()
#
#     import MyStrategy
#     MyStrategy.main(policy_id, stock_code, startDate, endDate)
#     return 'Hello World!'
#
#
#
#
#
#
#
#
#
#
#
#
#
# @app.route('/str',methods=['GET', 'POST'])
# def world():
#     data = request.get_json(silent=True)
#     print(data['startDate'])
#     #开始时间
#     startDate = request.args.get("startDate")
#     #endDate 结束时间
#     endDate = request.args.get("endDate")
#     #accountMoney 账户金额
#     accountMoney = request.args.get("accountMoney")
#     #commissionRate 佣金倍率
#     commissionRate = request.args.get("commissionRate")
#
#     #commissionRate 佣金倍率
#     init = request.args.get("init")
#
#
#
#
#     exec("""
# i = 1
# j = 2
# print(i + j)
# i = i ** 2
# j = j ** 2
# print('i + j = %a' % (i + j))""")
#     # print(startDate,endDate,accountMoney,commissionRate)
#     return 'Hello World!'


@app.route('/')
def hello_world():
    # MyStrategy.main(1,'sh.600010','20150101','20210101')
    # os.system('E:/Users/Administrator/PycharmProjects/flaskProject/MyStrategy.py')
    return 'Hello World!'


# 将数据格式化为JSON数据
def formatting_data(code, msg, data, ):
    return jsonify(
        {
            "code": code,
            "msg": msg,
            "data": data
        }
    )


if __name__ == '__main__':
    app.run(host='0.0.0.0', port=5000)
